﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;

namespace QLBenhVien
{
    class DAODonThuoc
    {
        public static string table_name = "DonThuoc";
        public static DataTable SearchByBenhNhan(DBODonThuoc o, DateTime date)
        {
            string sql = "select top 1 * from DonThuoc where benhnhan_id = @benhnhan_id and bacsi_id = @bacsi_id and ngaykham = @ngaykham";
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);                
                da.SelectCommand.Parameters.Add("@benhnhan_id", SqlDbType.Int).Value = o.benhnhan_id;
                da.SelectCommand.Parameters.Add("@bacsi_id", SqlDbType.Int).Value = o.bacsi_id;
                da.SelectCommand.Parameters.Add("@ngaykham", SqlDbType.DateTime).Value = date.ToShortDateString();
                da.Fill(dt);
            }
            catch (Exception ex) { return null; }
            return dt;
        }

        public static DataTable SearchByTen(string hoten)
        {
            string sql = "select * from DonThuoc join HoSoBenhNhan on HoSoBenhNhan.id = DonThuoc.benhnhan_id where HoSoBenhNhan.hoten like @hoten and ngaykham = @ngaykham";
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
                da.SelectCommand.Parameters.Add("@hoten", SqlDbType.NVarChar).Value = "%" + hoten + "%";                
                da.SelectCommand.Parameters.Add("@ngaykham", SqlDbType.DateTime).Value = DateTime.Now.ToShortDateString();
                da.Fill(dt);
            }
            catch (Exception ex) { return null; }
            return dt;
        }        

        public static int AddEmpty(DBODonThuoc o)
        {
            int id = 0;
            try
            {
                SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
                conn.Open();
                string sql = string.Format("insert into DonThuoc values({0},{1},'{2}',0,null,null)",o.benhnhan_id,o.bacsi_id,DateTime.Now.ToShortDateString());
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                
                sql = "select @@IDENTITY";                                
                cmd = new SqlCommand(sql, conn);
                id = int.Parse(cmd.ExecuteScalar().ToString());
                conn.Close();
            }
            catch (Exception ex)
            {
                ex.ToString();
                return 0;
            }
            return id;
        }

        public static void Add(DBODonThuoc o)
        {
            string sql = "select * from " + table_name;
            DataTable dt = DB_Provider.ReadSchema(sql);
            DataRow dr = dt.NewRow();
            foreach (PropertyDescriptor pro in TypeDescriptor.GetProperties(o))
            {
                dr[pro.Name] = pro.GetValue(o);
            }
            dt.Rows.Add(dr);
            DB_Provider.Write(dt, table_name);
        }

        public static void Save(DBODonThuoc o)
        {
            string sql = "select * from " + table_name;
            DataTable dt = DB_Provider.Read(sql);
            DataRow dr = dt.Rows.Find(o.id);
            dr.BeginEdit();
            foreach (PropertyDescriptor pro in TypeDescriptor.GetProperties(o))
            {
                if (pro.Name != "id")
                {
                    dr[pro.Name] = pro.GetValue(o);
                }
            }
            dr.EndEdit();            
            DB_Provider.Write(dt, table_name);
        }
    }

    class DAOChiTietDonThuoc
    {
        public static string table_name = "ChiTietDonThuoc";
        public static DataTable SearchByDonThuoc(int donthuoc_id)
        {
            string sql = string.Format("select * from {0} where donthuoc_id = @donthuoc_id",table_name);
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
                da.SelectCommand.Parameters.Add("@donthuoc_id", SqlDbType.Int).Value = donthuoc_id;                
                da.Fill(dt);
            }
            catch (Exception ex) { return null; }
            return dt;
        }

        public static void Add(DBOChiTietDonThuoc o)
        {
            string sql = "select * from " + table_name;
            DataTable dt = DB_Provider.ReadSchema(sql);
            DataRow dr = dt.NewRow();
            foreach (PropertyDescriptor pro in TypeDescriptor.GetProperties(o))
            {
                dr[pro.Name] = pro.GetValue(o);
            }
            dt.Rows.Add(dr);
            DB_Provider.Write(dt, table_name);
        }

        public static void Save(DBOChiTietDonThuoc o)
        {
            string sql = "select * from " + table_name;
            DataTable dt = DB_Provider.Read(sql);
            DataRow dr = dt.Rows.Find(o.id);
            dr.BeginEdit();
            foreach (PropertyDescriptor pro in TypeDescriptor.GetProperties(o))
            {
                if (pro.Name != "id")
                {
                    dr[pro.Name] = pro.GetValue(o);
                }
            }
            dr.EndEdit();
            DB_Provider.Write(dt, table_name);
        }
    }
}
